package com.qs.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.hibernate.Session;

import com.infomanage.bean.FieldBean;
import com.infomanage.bean.SearchBean;
import com.infomanage.dao.BaseDAO;
import com.infomanage.exception.DaoException;
import com.infomanage.po.Department;
import com.infomanage.po.HibernateSessionFactory;
import com.infomanage.po.Person;
import com.jiuqi.webtree.util.StringUtils;
import com.qs.po.QsGtTest;
import com.qs.vo.ColligateAnalResultItem;
import com.qs.vo.IndividualAnalResultItem;
import com.qs.vo.QsGtTestItem;

public class QsGtTestDAO extends BaseDAO<QsGtTest> {

	@SuppressWarnings("unchecked")
	public QsGtTestItem find(int id) throws DaoException {
		try {
			SearchBean searchBean = new SearchBean();
			FieldBean fieldBean = new FieldBean();
			fieldBean.setName("qsGttId");
			fieldBean.setValue(id);
			searchBean.add(fieldBean);
			List result = super.findByFields(new Class[] { Person.class,
					Person.class, Department.class }, QsGtTest.class,
					new String[] { "personId", "personId", "deptId" },
					new String[] { "personId", "trainee", "deptId" },
					searchBean);
			List<QsGtTestItem> items = parseResult(result);
			return items.size() == 0 ? null : items.get(0);
		} catch (Exception e) {
			throw new DaoException(e);
		} finally {
			HibernateSessionFactory.closeSession();
		}
	}

	@SuppressWarnings("unchecked")
	public List<QsGtTestItem> findByType(int type) throws DaoException {
		try {
			SearchBean searchBean = new SearchBean();
			FieldBean fieldBean = new FieldBean();
			fieldBean.setName("qsGttType");
			fieldBean.setValue(type);
			searchBean.add(fieldBean);
			List result = super.findByFields(new Class[] { Person.class,
					Person.class, Department.class }, QsGtTest.class,
					new String[] { "personId", "personId", "deptId" },
					new String[] { "personId", "trainee", "deptId" },
					searchBean);
			List<QsGtTestItem> items = parseResult(result);
			return items;
		} catch (Exception e) {
			throw new DaoException(e);
		} finally {
			HibernateSessionFactory.closeSession();
		}
	}

	@SuppressWarnings("unchecked")
	public List<QsGtTestItem> findByFields(SearchBean searchBean)
			throws DaoException {
		List result = super.findByFields(new Class[] { Person.class,
				Person.class, Department.class }, QsGtTest.class, new String[] {
				"personId", "personId", "deptId" }, new String[] { "personId",
				"trainee", "deptId" }, searchBean);
		return parseResult(result);
	}

	@SuppressWarnings("unchecked")
	private List<QsGtTestItem> parseResult(List result) {
		Iterator it = result.iterator();
		List<QsGtTestItem> list = new ArrayList<QsGtTestItem>();
		while (it.hasNext()) {
			Object[] o = (Object[]) it.next();
			QsGtTest bean = (QsGtTest) o[0];
			Person person = (Person) o[1];
			Person trainee = (Person) o[2];
			Department dept = (Department) o[3];
			QsGtTestItem item = new QsGtTestItem();
			item.setBean(bean);
			item.setPerson(person);
			item.setTrainee(trainee);
			item.setDept(dept);
			list.add(item);
		}
		return list;
	}

	public List<IndividualAnalResultItem> findByTraineeAndDate(String[] traineeIds,
			String beginDate, String endDate, int type) throws DaoException {
		if (traineeIds == null || traineeIds.length == 0) {
			return null;
		}
		String sql = "select T1.trainee,T2.name,T1.qs_Gtt_Date,T1.QS_GET_SCORE from Qs_Gt_Test" + " as T1 inner join person"
				+ " as T2 on T2.person_Id = T1.trainee"
				+ " inner join department as T3 on T1.dept_Id = T3.dept_Id "
				+ " where T1.trainee in (";
		StringBuilder sb = new StringBuilder(sql);
		for (int i = 0; i < traineeIds.length; i++) {
			if (StringUtils.isNotEmpty(traineeIds[i])) {
				if (i != 0) {
					sb.append(",");
				}
				sb.append(traineeIds[i]);
			}
		}
		sb.append(")");
		sb.append(" and qs_Gtt_Date >= '" + beginDate + "'");
		sb.append(" and qs_Gtt_Date <= '" + endDate + "'");
		sb.append(" and qs_Gtt_Type = " + type);
		sb.append(" order by qs_Gtt_Date");
		
		List<IndividualAnalResultItem> list = new ArrayList<IndividualAnalResultItem>();
		Session session = HibernateSessionFactory.getSession();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			Connection conn = session.connection();
			ps = conn.prepareStatement(sb.toString());
			rs = ps.executeQuery(sb.toString());
			while(rs.next()) {
				IndividualAnalResultItem item = new IndividualAnalResultItem();
				item.setTraineeId(rs.getInt(1));
				item.setTraineeName(rs.getString(2));
				item.setDate(rs.getString(3));
				item.setScore(rs.getFloat(4));
				list.add(item);
			}
		} catch (Exception e) {
			throw new DaoException(e);
		} finally {
			try {
				rs.close();
				ps.close();
			} catch (SQLException e) {
				throw new DaoException(e);
			}
			HibernateSessionFactory.closeSession();
		}
		return list;
	}

	@SuppressWarnings("unchecked")
	public List<ColligateAnalResultItem> findByDeptAndDate(int deptId, String beginDate,
			String endDate) throws DaoException {
		List<ColligateAnalResultItem> list = new ArrayList<ColligateAnalResultItem>();
		String sql = "SELECT trainee,p.name, gt.dept_id, dept.path, qs_gtt_date, SUM(qs_get_score)"
				+ " FROM qs_gt_test gt" + " INNER JOIN department dept"
				+ " ON gt.dept_id = dept.dept_id" + " INNER JOIN person p"
				+ " ON gt.trainee = p.person_id" + " GROUP  BY trainee"
				+ " HAVING gt.dept_id = 10" + " and qs_Gtt_Date >= '"
				+ beginDate + "'" + " and qs_Gtt_Date <= '" + endDate + "'";
		Session session = HibernateSessionFactory.getSession();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			Connection conn = session.connection();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery(sql);
			while(rs.next()) {
				ColligateAnalResultItem item = new ColligateAnalResultItem();
				item.setTraineeId(rs.getInt(1));
				item.setTraineeName(rs.getString(2));
				item.setDeptId(rs.getInt(3));
				item.setDeptName(rs.getString(4));
				item.setDate(rs.getString(5));
				item.setScore(rs.getFloat(6));
				list.add(item);
			}
		} catch (Exception e) {
			throw new DaoException(e);
		} finally {
			try {
				rs.close();
				ps.close();
			} catch (SQLException e) {
				throw new DaoException(e);
			}
			HibernateSessionFactory.closeSession();
		}

		return list;
	}

	public int count(SearchBean searchBean) {
		return super.count(new Class[] { Person.class, Person.class,
				Department.class }, QsGtTest.class, new String[] { "personId",
				"personId", "deptId" }, new String[] { "personId", "trainee",
				"deptId" }, searchBean);
	}
}
